#/usr/bin/env python
# -*- coding: UTF-8 -*-

'''
Copyright (C) 2010 dbzhang800@gmail.com
All rights reserved.
''' 

# 第一个操作 微软Excel 的程序，借此学习通过 Python win32 com 操作Excel
# 问题：
#  * 打开 xlsx 文件时，用相对路径出错，改用绝对路径
#  * 
# 2010.07.21

import os
import os.path
import sys
from win32com.client import Dispatch

# relative path not work! 
scirpt_dir = os.path.dirname(unicode(os.path.abspath(__file__), sys.getfilesystemencoding( )))
xls_file = os.path.join(scirpt_dir, u"WD与物镜激励及FOV视场关系.xlsx")
xlsApp = Dispatch("Excel.Application")
xlsApp.Visible = 1
xlsBook = xlsApp.Workbooks.Open(xls_file)
print "OK ", xls_file, "opened"

objSheet = xlsBook.Sheets(1)
fovSheet = xlsBook.Sheets(2)
land_energy_list = []
elens_filename_list = []
# first method
use_first = False
if use_first:
    for row in range(2, 1000):
        v = objSheet.Cells(row, 1).Value
        if not v:
            break
        land_energy_list.append(v)
else:
    values = objSheet.Range('A2:A1000').Value
    land_energy_list = [v[0] for v in values if v[0]]
print land_energy_list


values = objSheet.Range('B1:ZZ1').Value
elens_filename_list = [v for v in values[0] if v]
print elens_filename_list

# third mathod

rows = objSheet.UsedRange.Rows.Count
cols = objSheet.UsedRange.Columns.Count
print rows, cols


#fowllowing not works , ok ,not forget Value!
col1 = objSheet.UsedRange.Columns(1).Value
land_energy_list = [v[0] for v in col1]
land_energy_list.pop(0)
row1 = objSheet.UsedRange.Rows(1).Value
elens_filename_list = [v for v in row1[0]]
elens_filename_list.pop(0)
print land_energy_list
print elens_filename_list
#print col1, row1

objSheet.Cells(20, 2).Value = 20

xlsBook.Save()
